using System;
using System.Collections.Generic;
using System.Text;
using CrudI.Interface;

namespace CrudCreator.Creators
{
    public class CreateSqlSelectById : AbsSqlProcedure
    {
        private CreateSqlSelectById(string procedurePrefix)
            : base("SelectById", procedurePrefix)
        {
            sbProcedure = new StringBuilder();
        }

        /// <summary>
        /// Gets the select by id procedure for the table.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="databaseName">Name of the database.</param>
        /// <param name="procedurePrefix">The procedure prefix.</param>
        /// <returns></returns>
        public static string GetSelectByIdProcedure(ITable table, string databaseName, string procedurePrefix)
        {
            CreateSqlSelectById selectObject = new CreateSqlSelectById(procedurePrefix);
            selectObject.sbProcedure.Append(selectObject.procedureBegining + " ");
            selectObject.sbProcedure.AppendLine(databaseName + "." + table.TableName + selectObject.ProcedureType + " "  + Environment.NewLine);

            selectObject.sbProcedure.AppendLine("CREATE PROCEDURE " + databaseName + "." + table.TableName + selectObject.ProcedureType + Environment.NewLine);
            selectObject.BuildSelect(table);
            selectObject.procedureEnd = Environment.NewLine + " END" + Environment.NewLine;
            selectObject.sbProcedure.Append(selectObject.procedureEnd);
            return selectObject.sbProcedure.ToString();
        }

        private void BuildSelect(ITable table)
        {
            bool pkFound = false;
            sbProcedure.Append("\tSELECT ");
            for (int i = 0; i < table.ColumnList.Count; i++)
            {
                if (i + 1 < table.ColumnList.Count)
                    sbProcedure.Append(table.ColumnList[i].ColumnName + ", ");
                else
                    sbProcedure.Append(table.ColumnList[i].ColumnName);
            }
            sbProcedure.AppendLine(Environment.NewLine + "\tFROM " + table.TableName);

            string whereClause = string.Empty;

            for (int i = 0; i < table.ColumnList.Count; i++)
            {
                if (table.ColumnList[i].IsPK)
                {
                    whereClause = "\tWHERE " + table.ColumnList[i].ColumnName + " = " + procedurePrefix + table.ColumnList[i] + ";";
                    pkFound = true;
                    break;
                }
            }

            if (!pkFound)
                whereClause = "\t/*PK WAS NOT FOUND */" + Environment.NewLine + 
                    "\tWHERE " + "<field>" + " = " + "<parameter>;";
            sbProcedure.AppendLine(whereClause);
                
        }
    }
}
